There are few operators in T-SQL that cause developers to scramble for documentation more than PIVOT and UNPIVOT. Beyond documentation, transforming columns into rows (and vice-versa) can often be confusing and frustrating for those of us tasked with reformatting data for use by an application.

This article walks through PIVOT and UNPIVOT, providing examples of simple use cases for both – as well as some more complex scenarios we can run into in real-world data. These can be extraordinarily useful ways to reformat data efficiently and quickly with less code than the alternatives. So, there is no need to fear them again!

Where are the T-SQL PIVOT and UNPIVOT operators used?

Both the PIVOT and UNPIVOT operators are unique in that they are long-standing inclusions in the T-SQL surface area, but are not found in ANSI-SQL or many of the other popular flavors of SQL.

PIVOT and UNPIVOT are included in:

Why do developers struggle with PIVOT and UNPIVOT?

Despite being ubiquitous in T-SQL, developers are often confused or intimidated by the syntax. Being functions that are not often used, it’s easy to forget how to use them and struggle to piece together a solution using Microsoft Learn, articles, and trial-and-error. It’s equally easy to avoid these functions in favor of other solutions that involve more frequently-used syntax.

Usage and examples of PIVOT in T-SQL

The most common usage of PIVOT is during analytics or reporting when there is a need to convert row-based data into column-based data for output or further processing.

Consider a set of weekly sales data in WideWorldImporters for the following query:

The results show sales quantities per customer per day:

This is a simple data set with only three columns to manage. Consider a request for analytic data where there’s a need to have one row per customer with a column for each date, and total quantity sold on each. You’re provided with a sample format like this:

The blank boxes in the report are the total quantity sold on a given date for a customer. This is a natural use-case for PIVOT, as we want to convert row-based data into column-based data while summing up quantities along the way.

The basic format of PIVOT is as follows:

Following that formula, the following are the components needed to write our PIVOT query:

[Column List]: We will use * for this example, though you can customize as needed (as shown later.)

[Select Query]: This will be the same query we ran earlier, minus the ORDER BY clause. This will usually be similar to the already-identified/unmodified original query that retrieves the required data.

AggregateFunction: We want to add up the quantities per customer per date, so this will be SUM. If the source data doesn’t require aggregation, an aggregate function must still be provided here! In those scenarios, simply use whatever aggregate function is simplest, and add a comment to indicate that its choice does not matter.

[Column to Aggregate]: Since we are summing up Quantity, that’ll be the column here.

[Column to Pivot]: Since we are converting date values into columns, we will use [Invoice Date Key] here.

[Value List]: This is an explicit list of date values to pivot into columns. For this example, it is [3/8/2015], [3/9/2015], [3/10/2015], [3/11/2015],                                       [3/12/2015], [3/13/2015], [3/14/2015].

When all of the above values are plugged into the PIVOT query structure, the result is:

Note that the square brackets are a required part of this syntax. Using quotes or another delimiter will result in a highly ambiguous error message that looks like this:

Yeah, that is quite unhelpful, so be sure to use square brackets around each value in the list!

Square brackets are expected because these identifiers are both the values to filter for, and the column names that will be used in the result set.

The results of the pivot query are as follows:

What about a date with no sales?

For a date with no sales, the behavior will mimic how the aggregate function would behave if no source values are found. A SUM across no rows returns NULL – therefore the example here shows NULL when there are no sales on a given date for a customer.

If zeroes are preferable, then the initial SELECT can be adjusted from * to a modified column list:

The results are cleaner and show zeroes instead of NULL:

Multiple columns can be pivoted at once, if needed. For example, if quantity needs to be summed up (as shown above), and [Bill To Customer Key] also included alongside it, then this can be accomplished using two consecutive pivots:

This is an unusual scenario, but a single T-SQL query can contain as many pivots as you need. Realistically, I’d consider separating each into its own data, stage it in a temporary table, and then join it all together. This would simplify the overall query and improve performance.

Usage and examples of UNPIVOT in T-SQL

UNPIVOT operates similarly to PIVOT, except that columns are resolved into row-based data. So, essentially, it performs the reverse of what’s demonstrated above.

Consider the structure of the table Fact.Purchase:

Consider a scenario where we want to adjust this data such that each row is broken into multiple rows per Purchase Key, with a separate row for Ordered Outers, Ordered Quantity, and Received Outers. This is a common scenario when there’s a desire to split data into distinct files, tables, or sets – and do so with as little code as possible. It also allows for deformalizing analytic data for use in reports, dashboards, or AI.

The following query accomplishes the above task:

Note that the syntax is simpler than PIVOT in that aggregation is not necessary to generate a result set. Since UNPIVOT is a ‘one-to-many’ relationship between input and output, the query functions naturally as-is.

The results are as follows:

Note that each row from the input has been converted into three separate rows, one for each derived quantity. The quantity numbers themselves are not different from how they appeared in the underlying data. They are, however, now split into a row-per-value.

If there happened to be NULLs for any of the unpivoted values, then they could be omitted in the results with a single added WHERE clause:

In the above example, all three source quantity columns are NOT NULL. If any could be NULL, then this would ensure that the output does not contain any NULLs for Quantity.

In this regard, UNPIVOT can be a useful way to remove unknowns from a result set when NULL is deemed problematic.

The ‘unknown’ column list

When PIVOT operators are used, a new challenge is presented: an explicit column list must be provided. For scenarios where that value list is short, static, and unchanging, this is not an issue.

However – what if values are added or removed periodically?

Without updating the hard-coded list of values in the PIVOT query, the result set will be missing values. There’s no way to replace the column list with a * or other wildcard character.

This is a scenario where dynamic SQL can rescue us from a whole lot of head-scratching. The simplest solution using dynamic SQL would use the following steps:

Note that the T-SQL that is ultimately executed will look very similar to the PIVOT queries from above, despite the added complexity:

At first glance, this T-SQL looks very complex. To put it in perspective, though, consider what the PRINT returns as the script to be executed:

While its generation was complex, the resulting query was no different than PIVOT queries from earlier, aside from the data list having been customized.

The PIVOT column list can also be generated using a query rather than an explicit list:

The absurdly wide result set will look like this:

Generally speaking, a data set with 1,000+ columns is a very bad idea 😊

When generating dynamic PIVOT data, it’s important to ensure that the column list cannot grow unbounded. A WHERE clause on the INSERT into the temporary table would accomplish that easily enough:

That adjustment ensures that the pivoted column list contains only data from May 2016. It will, therefore, only output data that produces no more than 31 possible columns.

As an aside, the unbounded query that generated 1000+ columns did crash my SQL Server Management Studio. I’m sure the query optimizer and SQL output generator were not very happy with my life choices! 😊

Dynamic UNPIVOT is generated in the same fashion as dynamic PIVOT, with the added work being to generate a column list that gets inserted into the UNPIVOT query. As an alternative to a temporary table, a string that’s built using the required values – whatever they happen to be – could be generated instead.

Whether you choose a table or string depends solely on your T-SQL coding comfort and/or whatever is an easier method to collect the desired values.

In conclusion…

Despite the initial headaches that PIVOT and UNPIVOT often cause developers and database professionals, there’s no reason to avoid these operators. They’re very efficient for columns that need to be resolved into rows, or row-based data flipped into columns.

Even if your coding experience has never required the use of transformations like this, simply consider them usable, accessible, and less complex than they may seem at first glance. They may one day be the source of a brilliant data solution for you!